package de.dhbwstuttgart.vincon.telefoncompany;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.junit.Test;

import com.db4o.ObjectContainer;
import com.db4o.query.Query;

import de.dhbwstuttgart.vincon.telefoncompany.model.Call;
import de.dhbwstuttgart.vincon.telefoncompany.model.Customer;
import de.dhbwstuttgart.vincon.telefoncompany.model.Phone;
import de.dhbwstuttgart.vincon.telefoncompany.utils.DataCreatorDb4o;
import de.dhbwstuttgart.vincon.telefoncompany.utils.DataCreatorH2;
import de.dhbwstuttgart.vincon.telefoncompany.utils.DbHelper;

public class UseCases {

	private static int factor = 100;
	private static final boolean DEBUG = true;

	@Test
	public void runSuiteOverFactors() {
		factor = 100;
		runSuite();
		factor = 20;
		runSuite();
		factor = 10;
		runSuite();
		factor = 5;
		runSuite();
		factor = 2;
		runSuite();

	}

	@Test
	public void runSuite() {
		System.out.println("Run usecases with factor " + factor);
		createD4bo();
		createD4boWithIndexes();
		createH2();
		createH2WithIndex();
		System.out.println();
		getPhonesOfCustomer();
		System.out.println();
		createBills();
		System.out.println();
		getCallActivityOfCustomer();
		System.out.println();
		deleteOneMonth();
		System.out.println();
		System.out
				.println("####################################################################");
		System.out.println();
	}

	@Test
	public void createH2() {
		// print("Create the h2 database");

		new File(DbHelper.h2File.getAbsolutePath() + ".h2.db").delete();
		DbHelper.openDb(DbHelper.H2);

		long zeit = System.nanoTime();

		DbHelper.createDb(DbHelper.getH2());
		DataCreatorH2 dataCreator = new DataCreatorH2();

		dataCreator.createData(factor, DbHelper.getH2());

		Statement stat;
		try {
			stat = DbHelper.getH2().createStatement();
			ResultSet resultSet = stat
					.executeQuery("SELECT count(customer.id) from customer");
			while (resultSet.next()) {
				// print("Customer: " + resultSet.getInt(1));
			}
			resultSet = stat.executeQuery("SELECT count(phone.id) from phone");
			while (resultSet.next()) {
				// print("Phones: " + resultSet.getInt(1));
			}
			resultSet = stat.executeQuery("SELECT count(call.id) from call");
			while (resultSet.next()) {
				// print("Calls: " + resultSet.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Creation h2 in: "
				+ zeit
				+ " ms Filesize: "
				+ new File(DbHelper.h2File.getAbsolutePath() + ".h2.db")
						.length() / 1024 + " kb");
		// print("");
	}

	@Test
	public void createH2WithIndex() {
		// print("Create the h2 database with indexes");

		new File(DbHelper.h2WithIndexFile.getAbsolutePath() + ".h2.db")
				.delete();
		DbHelper.openDb(DbHelper.H2WITHINDEX);

		long zeit = System.nanoTime();

		DbHelper.createDbWithIndex(DbHelper.getH2WithIndexes());
		DataCreatorH2 dataCreator = new DataCreatorH2();

		dataCreator.createData(factor, DbHelper.getH2WithIndexes());

		Statement stat;
		try {
			stat = DbHelper.getH2WithIndexes().createStatement();
			ResultSet resultSet = stat
					.executeQuery("SELECT count(customer.id) from customer");
			while (resultSet.next()) {
				// print("Customer: " + resultSet.getInt(1));
			}
			resultSet = stat.executeQuery("SELECT count(phone.id) from phone");
			while (resultSet.next()) {
				// print("Phones: " + resultSet.getInt(1));
			}
			resultSet = stat.executeQuery("SELECT count(call.id) from call");
			while (resultSet.next()) {
				// print("Calls: " + resultSet.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Creation h2 indexed in: "
				+ zeit
				+ " ms Filesize: "
				+ new File(DbHelper.h2WithIndexFile.getAbsolutePath()
						+ ".h2.db").length() / 1024 + " kb");
		// print("");
	}

	@Test
	public void createD4bo() {
		// print("Create the db4o database");

		DbHelper.db4oFile.delete();
		DbHelper.openDb(DbHelper.DB4O);

		long zeit = System.nanoTime();
		DataCreatorDb4o dataCreator = new DataCreatorDb4o();

		try {
			dataCreator.createData(factor, DbHelper.getDb4o());
			// print(DbHelper.getDb4o().query(Customer.class).size()
			// + " Customers saved in the database");
			// print(DbHelper.getDb4o().query(Phone.class).size()
			// + " Phones saved in the database");
			// print(DbHelper.getDb4o().query(Call.class).size()
			// + " Calls saved in the database");
		} finally {
			DbHelper.getDb4o().close();
		}

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Creation db4o in: " + zeit + " ms Filesize: "
				+ DbHelper.db4oFile.length() / 1024 + " kb");
		// print("");
	}

	@Test
	public void createD4boWithIndexes() {
		// print("Create the db4o database with indexes");

		DbHelper.db4oIndexesFile.delete();
		DbHelper.openDb(DbHelper.DB4OWITHINDEX);

		long zeit = System.nanoTime();
		DataCreatorDb4o dataCreator = new DataCreatorDb4o();

		try {
			dataCreator.createData(factor, DbHelper.getDb4oWithIndexes());
			// print(DbHelper.getDb4oWithIndexes().query(Customer.class).size()
			// + " Customers saved in the database");
			// print(DbHelper.getDb4oWithIndexes().query(Phone.class).size()
			// + " Phones saved in the database");
			// print(DbHelper.getDb4oWithIndexes().query(Call.class).size()
			// + " Calls saved in the database");
		} finally {
			DbHelper.getDb4oWithIndexes().close();
		}

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Creation db4o indexed in: " + zeit
				+ " ms Filesize: " + DbHelper.db4oIndexesFile.length() / 1024
				+ " kb");
		// print("");
	}

	@Test
	public void getPhonesOfCustomer() {
		long zeit = System.nanoTime();

		// print("Get phones of customer Max Mustermann from the db4o database");
		DbHelper.openDb(DbHelper.DB4O);
		if(factor < 100)
		getPhonesOfCustomerDb4o(DbHelper.getDb4o());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Get Phone from db4o in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Get phones of customer Max Mustermann from the db4o database with indexes");
		DbHelper.openDb(DbHelper.DB4OWITHINDEX);
		getPhonesOfCustomerDb4o(DbHelper.getDb4oWithIndexes());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Get Phone from db4o indexed in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Get phones of customer Max Mustermann from the h2 database");
		DbHelper.openDb(DbHelper.H2);
		getPhonesOfCustomerH2(DbHelper.getH2());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Get Phone from h2 in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Get phones of customer Max Mustermann from the h2 database with indexes");
		DbHelper.openDb(DbHelper.H2WITHINDEX);
		getPhonesOfCustomerH2(DbHelper.getH2WithIndexes());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Get Phone from h2 indexed in: " + zeit + " ms");
		// print("");
	}

	private void getPhonesOfCustomerH2(Connection conn) {
		try {
			Statement stat = conn.createStatement();

			ResultSet resultSet = stat
					.executeQuery("SELECT number FROM customer, phone WHERE customer.id = phone.customerNr AND customer.surname = 'Mustermann' AND customer.forename = 'Max'");

			while (resultSet.next()) {
				// print(resultSet.getString(1));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void getPhonesOfCustomerDb4o(ObjectContainer db) {
		Customer customer = null;

		try {
			Query query = db.query();
			query.constrain(Customer.class);
			query.descend("forename").constrain("Max").equal();
			query.descend("surname").constrain("Mustermann").equal();

			List<Phone> resultatCustomer = query.execute();
			customer = (Customer) resultatCustomer.toArray()[0];

			List<Phone> resultat = customer.getPhones();

			for (Phone phone : resultat) {
				// print("Phone: " + phone.getNumber());
			}

		} finally {
			db.close();
		}
	}

	@Test
	public void createBills() {
		int count = 0;

		long zeit = System.nanoTime();
		// print("Create bills from the db4o database");
		DbHelper.openDb(DbHelper.DB4O);
		if(factor < 100)
			count = createBillsDb4o(DbHelper.getDb4o());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Create " + count + " bills db4o in: " + zeit
				+ " ms");
		count = 0;
		// print("");

		zeit = System.nanoTime();

		// print("Create bills from the db4o database with indexes");
		DbHelper.openDb(DbHelper.DB4OWITHINDEX);
		
		count = createBillsDb4o(DbHelper.getDb4oWithIndexes());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Create " + count + " bills db4o indexed in: "
				+ zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Create bills from the h2 database");
		DbHelper.openDb(DbHelper.H2);
		createBillH2(DbHelper.getH2());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Create bills h2 in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Create bills from the h2 database with indexes");
		DbHelper.openDb(DbHelper.H2WITHINDEX);
		createBillH2(DbHelper.getH2WithIndexes());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Create bills h2 indexed in: " + zeit + " ms");
		// print("");
	}

	private void createBillH2(Connection conn) {
		try {
			Statement statCustomers = conn.createStatement();
			Statement statPhones = conn.createStatement();
			Statement statCalls = conn.createStatement();

			ResultSet phones;
			ResultSet calls;
			ResultSet customers = statCustomers
					.executeQuery("SELECT id, forename, surname FROM customer");

			while (customers.next()) {
				// print(customers.getString(2) + " " + customers.getString(3));

				phones = statPhones
						.executeQuery("SELECT id, number FROM phone WHERE phone.customerNr = "
								+ customers.getString(1));
				while (phones.next()) {
					// print("######### " + phones.getString(2) + " #########");

					calls = statCalls
							.executeQuery("SELECT start, end, DATEDIFF(second, start, end) as duration FROM call WHERE start < '2012-02-01 00:00:00' AND call.caller = "
									+ phones.getString(1));
					while (calls.next()) {
						// print(calls.getTimestamp(1).toString() + " - "
						// + calls.getTimestamp(2).toString() + " : "
						// + calls.getInt(3) + " sek");
					}

				}

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private int createBillsDb4o(ObjectContainer db) {
		long zeit = System.nanoTime();
		int count = 0;
		GregorianCalendar startDate = new GregorianCalendar(2012, 0, 0);

		try {
			Query queryCustomer = db.query();
			queryCustomer.constrain(Customer.class);

			List<Customer> resultatCustomer = queryCustomer.execute();

			Query queryCalls;
			List<Call> resultatCall;
			for (Customer customer : resultatCustomer) {
				List<Phone> resultatPhones = customer.getPhones();

//				print(customer.getForename() + " " + customer.getSurname());
				for (Phone phone : resultatPhones) {
					//print("######### " + phone.getNumber() + " #########");

					queryCalls = db.query();
					queryCalls.constrain(Call.class);
					queryCalls.descend("start")
					.constrain(startDate.getTimeInMillis()).greater();
					queryCalls.descend("caller").constrain(phone);
					

					resultatCall = queryCalls.execute();

					for (Call call : resultatCall) {
						long duration = (call.getEnd() - call.getStart()) / 1000;
//						print(new Date(call.getStart()).toString() + " - "
//						+ new Date(call.getEnd()).toString() + " : "
//						+ duration + " sek");
					}

				}

				count++;
				if ((System.nanoTime() - zeit) / 1000 / 1000 > (3600000 * 3)) {
					db.close();
					return count;
				}
			}

		} finally {
			db.close();
		}
		return count;
	}

	@Test
	public void deleteOneMonth() {
		long zeit = System.nanoTime();

		// print("Delete January from the db4o database");
		DbHelper.openDb(DbHelper.DB4O);
		if(factor < 100)
		deleteOneMonthDb4o(DbHelper.getDb4o());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Deletion db4o in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Delete January from the db4o database with indexes");
		DbHelper.openDb(DbHelper.DB4OWITHINDEX);
		if(factor < 100)
		deleteOneMonthDb4o(DbHelper.getDb4oWithIndexes());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Deletion db4o indexed in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Delete January from the h2 database");
		DbHelper.openDb(DbHelper.H2);
		deleteOneMonthH2(DbHelper.getH2());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Deletion h2 in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Delete January from the h2 database with indexes");
		DbHelper.openDb(DbHelper.H2WITHINDEX);
		deleteOneMonthH2(DbHelper.getH2WithIndexes());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Deletion h2 indexed in: " + zeit + " ms");
		// print("");
	}

	private void deleteOneMonthH2(Connection conn) {
		try {
			Statement stat = conn.createStatement();

			int deletedRows = stat
					.executeUpdate("DELETE FROM call WHERE start < '2012-02-01 00:00:00'");

			// print("Deleted rows: " + deletedRows);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void deleteOneMonthDb4o(ObjectContainer db) {
		GregorianCalendar startDate = new GregorianCalendar(2012, 0, 0);
		GregorianCalendar endDate = new GregorianCalendar(2012, 0, 31);

		try {
			Query query = db.query();
			query.constrain(Call.class);
			query.descend("start").constrain(startDate.getTimeInMillis())
					.greater();
			query.descend("end").constrain(endDate.getTimeInMillis()).smaller();

			List<Call> resultat = query.execute();
			for (Call call : resultat) {
				db.delete(call);
			}
			// print("Deleted " + resultat.size() + " Calls");
		} finally {
			db.close();
		}
	}

	@Test
	public void getCallActivityOfCustomer() {
		long zeit = System.nanoTime();
		// print("Get call activity of customer from the db4o database");
		DbHelper.openDb(DbHelper.DB4O);
		if(factor < 100)
		getCallActivityOfCustomerDb4o(DbHelper.getDb4o());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Call activity db4o in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Get call activity of customer  from the db4o database with indexes");
		DbHelper.openDb(DbHelper.DB4OWITHINDEX);
		getCallActivityOfCustomerDb4o(DbHelper.getDb4oWithIndexes());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Call activity db4o indexed in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Get call activity of customer  from the h2 database");
		DbHelper.openDb(DbHelper.H2);
		getCallActivityOfCustomerH2(DbHelper.getH2());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Call activity h2 in: " + zeit + " ms");
		// print("");

		zeit = System.nanoTime();

		// print("Get call activity of customer  from the h2 database with indexes");
		DbHelper.openDb(DbHelper.H2WITHINDEX);
		getCallActivityOfCustomerH2(DbHelper.getH2WithIndexes());

		zeit = (System.nanoTime() - zeit) / 1000 / 1000;
		System.out.println("Call activity h2 indexed in: " + zeit + " ms");
		// print("");
	}

	private void getCallActivityOfCustomerH2(Connection conn) {
		try {
			Statement statCallee = conn.createStatement();
			Statement statPhones = conn.createStatement();
			Statement statCaller = conn.createStatement();

			ResultSet caller;
			ResultSet callee;
			ResultSet phones = statPhones
					.executeQuery("SELECT phone.id, phone.number, forename, surname FROM phone, customer WHERE customer.id = phone.customerNr AND surname = 'Mustermann' AND forename = 'Max'");

			while (phones.next()) {
				// print("######### " + phones.getString(2) + " #########");

				caller = statCaller
						.executeQuery("SELECT forename, surname, start, end, DATEDIFF(second, start, end) as duration FROM call, phone, customer WHERE call.callee = phone.id AND customer.id = phone.customerNr AND call.caller = "
								+ phones.getString(1));
				while (caller.next()) {
					// print("Caller: " + caller.getString(1) + " "
					// + caller.getString(2) + " Callee: "
					// + phones.getString(3) + " " + phones.getString(4)
					// + " Details: " + caller.getTimestamp(3).toString()
					// + " - " + caller.getTimestamp(4).toString() + " : "
					// + caller.getInt(5) + " sek");
				}

				callee = statCallee
						.executeQuery("SELECT forename, surname, start, end, DATEDIFF(second, start, end) as duration FROM call, phone, customer WHERE call.caller = phone.id AND customer.id = phone.customerNr AND call.callee = "
								+ phones.getString(1));
				while (callee.next()) {
					// print("Caller: " + phones.getString(3) + " "
					// + phones.getString(4) + " Callee: "
					// + callee.getString(1) + " " + callee.getString(2)
					// + " Details: " + callee.getTimestamp(3).toString()
					// + " - " + callee.getTimestamp(4).toString() + " : "
					// + callee.getInt(5) + " sek");
				}

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void getCallActivityOfCustomerDb4o(ObjectContainer db) {
		Customer customer = null;

		try {
			Query query = db.query();
			query.constrain(Customer.class);
			query.descend("forename").constrain("Max").equal();
			query.descend("surname").constrain("Mustermann").equal();

			List<Phone> resultatCustomer = query.execute();
			customer = (Customer) resultatCustomer.toArray()[0];

			List<Phone> resultat = customer.getPhones();

			Query queryCalls;
			List<Call> resultatCall;
			for (Phone phone : resultat) {
				// print("######### " + phone.getNumber() + " #########");

				// Caller
				queryCalls = db.query();
				queryCalls.constrain(Call.class);
				queryCalls.descend("caller").constrain(phone);

				resultatCall = queryCalls.execute();

				for (Call call : resultatCall) {
					long duration = (call.getEnd() - call.getStart()) / 1000;
					// print("Caller: " +
					// call.getCaller().getCustomer().getName()
					// + " Callee: "
					// + call.getCallee().getCustomer().getName()
					// + " Details: "
					// + new Date(call.getStart()).toString() + " - "
					// + new Date(call.getEnd()).toString() + " : "
					// + duration + " sek");
				}

				queryCalls = db.query();
				queryCalls.constrain(Call.class);
				queryCalls.descend("callee").constrain(phone);

				resultatCall = queryCalls.execute();

				for (Call call : resultatCall) {
					long duration = (call.getEnd() - call.getStart()) / 1000;
					// print("Caller: " +
					// call.getCaller().getCustomer().getName()
					// + " Callee: "
					// + call.getCallee().getCustomer().getName()
					// + " Details: "
					// + new Date(call.getStart()).toString() + " - "
					// + new Date(call.getEnd()).toString() + " : "
					// + duration + " sek");
				}
			}

		} finally {
			db.close();
		}
	}

	private void print(String text) {
		if (DEBUG)
			System.out.println(text);
	}

}
